#encoding: utf-8

=begin
    对指定时间段内符合专业用户推荐新用户的合格替代后买销售额进行统计
    ruby gen-qualified-t2-sales.rb 2022-07-01 2022-07-31
=end

load 'rds_api.rb'
@rds=@rds_tt

load 'tutem_utils.rb'
load 'rep_utils.rb'

@sday = Date.today
@eday = Date.today
if ARGV.size < 2
    puts "Usage: ruby gen-qualified-t2-sales.rb [start_date] [end_date]"
    @sday = Date.parse('2023-01-01')
    @eday = Date.today
else
    @sday = Date.parse(ARGV[0])
    @eday = Date.parse(ARGV[1])
end

stime = unixtime_of_sday @sday
etime = unixtime_of_eday @eday

def get_user_info uid

    info = {}

    sql = "select uid, add_time, real_name, is_promoter from tutem_mall.eb_user where uid = #{uid}"
    (queryRds(sql)).each do |q|
        info.store 'uid', uid
        info.store 'real_name', q['real_name'].to_s
        info.store 'add_time', q['add_time']
        created_at = Time.at(q['add_time']).strftime('%Y-%m-%d %H:%M:%S')
        info.store 'created_at', created_at
        info.store 'is_promoter', q['is_promoter']
    end

    return {} if info['uid'].nil?

    fo_id = 0
    fo_name = ''
    fo_time = unixtime_of_eday Date.parse('2099-12-31')
    fo_at = '2099-12-31 23:59:59'
    fo_phone = ''
    sql = "select id, pay_time, real_name, user_phone from tutem_mall.eb_store_order where paid = 1 and uid = #{uid} and tags like '%NPO%'"
    (queryRds(sql)).each do |q|
        fo_id = q['id']
        fo_name = q['real_name']
        fo_time = q['pay_time']
        fo_at = Time.at(q['pay_time']).strftime('%Y-%m-%d %H:%M:%S')
        fo_phone = q['user_phone']
    end

    info.store 'fo_id', fo_id
    info.store 'fo_name', fo_name
    info.store 'fo_time', fo_time
    info.store 'fo_at', fo_at
    info.store 'fo_phone', fo_phone

    return info

end

def qualified_2tier_users_during stime, etime

    fo_uids = [] #所有在该时间段下过首单的新用户 fo: first_order
    sqlUfo = "select uid from tutem_mall.eb_store_order where tags like '%NPO%' and pay_time >= #{stime} and pay_time <= #{etime} and paid = 1"
    (queryRds(sqlUfo)).each { |q| fo_uids += [q['uid']] }
    fo_uids = fo_uids.uniq
    puts "在#{@sday}~#{@eday}期间下过首单的用户总数：#{fo_uids.size}"

    sqlPc = "select uid from tutem_mall.eb_user where spread_uid in (select uid from tutem_mall.eb_user where is_promoter = 1) and is_promoter = 0"
    theUids = [] #其中非专业会员（且推荐者的上级为专业会员）推荐产生的新用户 npc: non_professional_channel
    theList = {}
    sqlUnpc = "select u.uid, u.spread_uid as t1_uid, t2.spread_uid as t2_uid from tutem_mall.eb_user u left join tutem_mall.eb_user t2 on (u.spread_uid = t2.uid) where u.uid in (#{sqlUfo}) and u.spread_uid in (#{sqlPc})"
    (queryRds(sqlUnpc)).each do |q| 

        tu_uid = q['uid'] #tu: the user
        theUids += [tu_uid]

        t1_uid = q['t1_uid']
        t2_uid = q['t2_uid']

        info = {}

        tu = get_user_info tu_uid
        info.store 'tu_uid', tu_uid
        info.store 'tu_real_name', tu['real_name']
        info.store 'tu_add_time', tu['add_time']
        info.store 'tu_created_at', tu['created_at']
        info.store 'tu_is_promoter', tu['is_promoter']
        info.store 'tu_fo_time', tu['fo_time']
        info.store 'tu_fo_at', tu['fo_at']
        info.store 'tu_fo_id', tu['fo_id']
        info.store 'tu_fo_name', tu['fo_name']
        info.store 'tu_fo_phone', tu['fo_phone']

        t1 = get_user_info t1_uid
        info.store 't1_uid', t1_uid
        info.store 't1_real_name', t1['real_name']
        info.store 't1_add_time', t1['add_time']
        info.store 't1_created_at', t1['created_at']
        info.store 't1_is_promoter', t1['is_promoter']
        info.store 't1_fo_time', t1['fo_time']
        info.store 't1_fo_at', t1['fo_at']
        info.store 't1_fo_id', t1['fo_id']
        info.store 't1_name', t1['fo_name']
        info.store 't1_fo_phone', t1['fo_phone']

        t2 = get_user_info t2_uid
        info.store 't2_uid', t2_uid
        info.store 't2_real_name', t2['real_name']
=begin
        info.store 't2_add_time', t2['add_time']
        info.store 't2_created_at', t2['created_at']
        info.store 't2_is_promoter', t2['is_promoter']
        info.store 't2_fo_time', t2['fo_time']
        info.store 't2_fo_at', t2['fo_at']
        info.store 't2_fo_id', t2['fo_id']
        info.store 't2_name', t2['fo_name']
        info.store 't2_fo_phone', t2['fo_phone']
=end

        #检查标记一，用户下首单时上级推荐人并未购买过, 即用户的首单时间小于上级推荐人的首单时间
        flag_1 = info['tu_fo_time'] < info['t1_fo_time']
        #检查标记二，用户的创建时间大于上级推荐人的创建时间且不超过七天
        flag_2 = (info['tu_add_time'] > info['t1_add_time']) && (info['tu_add_time'] - info['t1_add_time'] < 7*24*60*60)
        #检查标记二，用户的首单时间大于上级推荐人的创建时间且不超过七天
        flag_3 = (info['tu_fo_time'] > info['t1_add_time']) && (info['tu_fo_time'] - info['t1_add_time'] < 7*24*60*60)
        flag_4 = true
        #检查标记四，如果上级推荐人有首单，用户首单的收件人电话与上级推荐人的首单电话需要一致（如果不一样，意味着是独立购买）
        flag_4 = info['tu_fo_phone'] == info['t1_fo_phone'] if info['t1_fo_phone'] != ''

        actual_paid = 0.00
        sqlAp = "select sum(pay_price) - sum(refund_price) as actual_paid from tutem_mall.eb_store_order where uid = #{tu_uid} and paid = 1 and pay_time >= #{stime} and pay_time <= #{etime} and pay_type = 'weixin'"
        (queryRds(sqlAp)).each do |i|
            actual_paid = i['actual_paid']
        end

        info.store 'actual_paid', actual_paid

        info.store 'flag_1', flag_1
        info.store 'flag_2', flag_2
        info.store 'flag_3', flag_3
        info.store 'flag_4', flag_4

        theList.store tu_uid, info if flag_1 && flag_2 && flag_3 && flag_4

    end

    puts "在#{@sday}~#{@eday}期间下过首单，且上级推荐人为非专业用户，二级推荐人为专业会员的用户总数：#{theUids.size}"
    puts "其中同时符合平替认定条件的用户数：#{theList.size}"

    return theList

end

checkList = qualified_2tier_users_during stime, etime #得到所有待检测
save_list_to_excel checkList, "qualifed-t2-sales-#{@sday}~#{@eday}.xls"

#如果是演习模式，将不做实际修正
is_drill = true
is_drill = false

ap checkList if is_drill
checkList.each do |uid, user|
    uid = user['tu_uid'].to_i
    user_add_date = Time.at(user['tu_add_time']).strftime('%Y-%m-%d')
    t1_uid = user['t1_uid'].to_i
    t2_uid = user['t2_uid'].to_i
    notice = "用户##{sprintf('%05d',uid)}经OMS审计，符合【专业用户新患间接介绍认可标准-2022Q3后】，自注册日#{user_add_date}起，推荐渠道由C##{sprintf('%05d',t1_uid)}改为C##{sprintf('%05d',t2_uid)}，统计标签已同步"
    if  is_drill
        puts "[DRILL MODE] #{notice}"
    else
        change_user_spreader uid, t2_uid, true #默认为同一个用户
        send_message_to_bot 'WWBOT_XT', notice, ['@all']
    end
end
